package com.gmail.wangzdl.DB;

import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.imageio.stream.FileImageInputStream;

public class AccessDemo {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		testInsert();
		testUpdate();
		testSelect();
		testDelete();

	}

	private static void testInsert() {
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			String url = "jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=mydb.mdb";
			Connection conn = DriverManager.getConnection(url, "", "");
			Statement stmt = conn.createStatement();

			PreparedStatement ps = conn
					.prepareStatement("insert into Employee(EmployeeName,photo) values(?,?)");
			ps.setString(1, "Tom");
			//FileInputStream in= new FileInputStream("E:\\My Documents\\QQgirl.jpg");
			FileInputStream in= new FileInputStream("c:\\repbr.dll");
			System.out.println(in.available());
			ps.setBinaryStream(2, in,in.available());
			ps.executeUpdate();
			ResultSet rs=stmt.executeQuery("SELECT @@IDENTITY AS LastId");
			while (rs.next()) {
				System.out.print("LastId");
				System.out.println(rs.getString("LastId"));
			}
			rs.close();
			ps.close();
			stmt.close();
			conn.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}

	}
	
	private static void testUpdate() {
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			String url = "jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=mydb.mdb";
			Connection conn = DriverManager.getConnection(url, "", "");
			Statement stmt = conn.createStatement();

			PreparedStatement ps = conn
					.prepareStatement("update Employee set employeename=?,photo=?");
			ps.setString(1, "Tom");
			FileInputStream in= new FileInputStream("E:\\My Documents\\QQgirl.jpg");
			System.out.println(in.available());
			byte[] file=new byte[in.available()];
			in.read(file);
			ps.setBytes(2, file);
			ps.executeUpdate();
			ResultSet rs=stmt.executeQuery("SELECT @@IDENTITY AS LastId");
			while (rs.next()) {
				System.out.print("LastId");
				System.out.println(rs.getString("LastId"));
			}
			rs.close();
			ps.close();
			stmt.close();
			conn.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}

	}

	private static void testSelect() {
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			String url = "jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=mydb.mdb";
			Connection conn = DriverManager.getConnection(url, "", "");
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM employee");
			System.out.println("testSelect");
			while (rs.next()) {
				System.out.println(rs.getString(1));
				System.out.println(rs.getString("employeename"));
				byte[] is=rs.getBytes("photo");				
				System.out.println(is.length);
				System.out.println(new String(is));
				System.out.println("===============");
				System.out.println(is[0]);
				System.out.println("===============");
				System.out.println(is[0]+"");
			}
			
			rs.close();

			stmt.close();
			conn.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	private static void testDelete() {
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

			String url = "jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=mydb.mdb";
			Connection conn = DriverManager.getConnection(url, "", "");
			Statement stmt = conn.createStatement();


			PreparedStatement ps = conn
					.prepareStatement("delete from employee");

			ps.executeUpdate();
			ps.close();

			stmt.close();
			conn.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}

	}
	


}
